if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TF_City_Select_by_province_id]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[TF_City_Select_by_province_id]
GO

CREATE PROCEDURE [dbo].[TF_City_Select_by_province_id] (
	@ID int
)

AS

SET NOCOUNT ON

SELECT
	[ID],
	[CityID],
	[CityName],
	[CountryID],
	[ProvinceID],
	[IpAddressL],
	[IpAddressH],
	[LanuageId],
	[Description],
	[LocCode],
	[Name]
FROM
	[TF_City]
WHERE
	[ProvinceID] = @ID
GO
------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TF_TripParamSelectByType]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[TF_TripParamSelectByType]
GO

CREATE PROCEDURE [dbo].[TF_TripParamSelectByType] (
	@type varchar(5)
)

AS

SET NOCOUNT ON

SELECT
	[ID],
	[text],
	[type]
FROM
	[TF_TripParam]
WHERE
	[type] = @type
GO
------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TF_ViewSelectByCityId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[TF_ViewSelectByCityId]
GO

CREATE PROCEDURE [dbo].[TF_ViewSelectByCityId] (
	@CityId int,
	@PageSize int = 10,
	@PageIndex int = 1
)

AS

SET NOCOUNT ON

select 
	* 
from 
	(select tv.*,row_number() over(order by id) rowid  from tf_view tv where tv.CityID = @CityId) T 
where T.rowid between ((@PageIndex)*(@PageSize)-(@PageSize)+1) and (@PageIndex)*(@PageSize)  

GO
------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TF_ViewSelectByCityIdCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[TF_ViewSelectByCityIdCount]
GO

CREATE PROCEDURE [dbo].[TF_ViewSelectByCityIdCount] (
	@CityId int
)

AS

SET NOCOUNT ON

select count(*) from tf_view where CityID=@CityId

GO
------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TF_SelectAllCityView]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[TF_SelectAllCityView]
GO

CREATE PROCEDURE [dbo].[TF_SelectAllCityView] 

AS

SET NOCOUNT ON

select tv.cityId, tv.id,(select name from tf_city tc where tc.id=tv.cityId)+','+tv.name CityView from tf_view tv where tv.id<11700

GO
------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TF_EventDetailSelectByEhId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[TF_EventDetailSelectByEhId]
GO

CREATE PROCEDURE [dbo].[TF_EventDetailSelectByEhId] (
	@ehId int
)

AS

SET NOCOUNT ON

SELECT
	tfe.[id],
	tfe.[EventHId],
	tfe.[ToCityId],
	tfe.[PreCityId],
	tfe.[StartDate],
	tfe.[EndDate],
	tfe.[DayNumber],
	(select name from tf_city c where c.id=tfe.[ToCityId]) ToCityName
FROM
	[TF_EventDetail] tfe
WHERE
	tfe.[EventHId] = @ehId
ORDER BY tfe.[DayNumber]
GO
------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TF_EventViewSelectByCityId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[TF_EventViewSelectByCityId]
GO

CREATE PROCEDURE [dbo].[TF_EventViewSelectByCityId] (
	@CityID int
)

AS

SET NOCOUNT ON

SELECT
	[id],
	[CityID],
	[ViewId],
	[ViewPrice],
	[StartDate],
	[EndDate],
	[Memo],
	(select v.name from tf_view v where v.id=[TF_EventView].[ViewId]) ViewName
FROM
	[TF_EventView]
WHERE
	[CityID] = @CityID
GO
------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TF_LeaveWordSelectByEleId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[TF_LeaveWordSelectByEleId]
GO

CREATE PROCEDURE [dbo].[TF_LeaveWordSelectByEleId] (
	@eleId int,
	@eleType varchar(50),
	@PageSize int,
	@PageIndex int
)

AS

SET NOCOUNT ON

select * from (select 
	t.*,
	IsNull(up.nick_name,'') nick_name,
	isnull(up.pic,'') pic, 
	row_number() over(order by t.id) rowid
from 
	tf_leavewords t left join tf_userProfile up on t.uid=up.uid
where
	t.elementId = @eleId and 
	t.elementType = @eleType) T
where T.rowid between ((@PageIndex)*(@PageSize)-(@PageSize)+1) and (@PageIndex)*(@PageSize) 
GO

---

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TF_LeaveWordSelectByEleIdCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[TF_LeaveWordSelectByEleIdCount]
GO

CREATE PROCEDURE [dbo].[TF_LeaveWordSelectByEleIdCount] (
	@eleId int,
	@eleType varchar(50)
)

AS

SET NOCOUNT ON

select count(*) cont from (select 
	t.*,
	IsNull(up.nick_name,'') nick_name,
	isnull(up.pic,'') pic
from 
	tf_leavewords t left join tf_userProfile up on t.uid=up.uid
where
	t.elementId = @eleId and 
	t.elementType = @eleType) T
GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].TF_EventHDeleteAll') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].TF_EventHDeleteAll
GO

CREATE PROCEDURE [dbo].TF_EventHDeleteAll (
	@Id int
)

AS

SET NOCOUNT ON

delete from tf_eventview where cityid in (select tocityid from tf_eventdetail where eventhid=@id);
delete from tf_eventdetail where eventhid=@id;
DELETE FROM [TF_EventH] WHERE [Id] = @Id

GO
------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].TF_EventHDeleteAll') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].TF_EventHDeleteAll
GO

CREATE PROCEDURE [dbo].TF_EventHDeleteAll (
	@Id int,
	@uid int
)

AS

SET NOCOUNT ON

delete from tf_eventview where cityid in (select ed.tocityid from tf_eventdetail ed inner join tf_eventh h on h.id=ed.eventhid where h.id=@id and h.ownerid=@uid);
delete from tf_eventdetail where id in (select ed.id from tf_eventdetail ed inner join tf_eventh h on ed.eventhid=h.id where h.id=@id and h.ownerid=@uid);
DELETE FROM [TF_EventH] WHERE [Id] = @Id and ownerid=@uid;

GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].TF_SelectAllEventH') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].TF_SelectAllEventH
GO

CREATE PROCEDURE [dbo].TF_SelectAllEventH (
	@PageSize int = 10,
	@PageIndex int = 1,
	@UserId int
)

AS

SET NOCOUNT ON

select 
	* 
from 
	(select h.*,row_number() over(order by h.id) rowid from tf_eventh h where h.ownerid=@UserId) T 
where T.rowid between ((@PageIndex)*(@PageSize)-(@PageSize)+1) and (@PageIndex)*(@PageSize)  

GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].TF_UserProfileSelectNewFriends') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].TF_UserProfileSelectNewFriends
GO

CREATE PROCEDURE [dbo].TF_UserProfileSelectNewFriends (
	@PageSize int = 9,
	@PageIndex int = 1
)

AS

SET NOCOUNT ON

select 
	* 
from 
	(select h.*,row_number() over(order by h.keyin_date desc) rowid from tf_userprofile h) T 
where T.rowid between ((@PageIndex)*(@PageSize)-(@PageSize)+1) and (@PageIndex)*(@PageSize)  

GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].TF_SelectCountryViewCountByCountryId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].TF_SelectCountryViewCountByCountryId
GO

CREATE PROCEDURE [dbo].TF_SelectCountryViewCountByCountryId (
	@countryId int
)

AS

SET NOCOUNT ON

select T.CountryId id, 
       (select c.countryname from tf_country c where c.id=T.countryid) [name],
	   count(*) ViewCount from 
(select ed.*,
	   (select tc.countryId from tf_city tc where tc.id=ed.toCityId) CountryId,
       (select tc.provinceId from tf_city tc where tc.id=ed.toCityId) ProvinceId
 from tf_eventdetail ed) T where T.CountryId=@countryId group by T.CountryId

GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].TF_SelectProvinceViewCountByCountryId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].TF_SelectProvinceViewCountByCountryId
GO

CREATE PROCEDURE [dbo].TF_SelectProvinceViewCountByCountryId (
	@countryId int
)

AS

SET NOCOUNT ON

select T.ProvinceId id,
	   (select tp.provice_name from tf_province tp where tp.id=T.ProvinceId) [name],
	   count(*) ViewCount from 
(select ed.*,
	   (select tc.countryId from tf_city tc where tc.id=ed.toCityId) CountryId,
       (select tc.provinceId from tf_city tc where tc.id=ed.toCityId) ProvinceId
 from tf_eventdetail ed) T where T.countryid=@countryId group by T.ProvinceId

GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].TF_SelectCityViewCountByProvinceId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].TF_SelectCityViewCountByProvinceId
GO

CREATE PROCEDURE [dbo].TF_SelectCityViewCountByProvinceId (
	@provinceId int
)
as 

SET NOCOUNT ON

select T.toCityId id,
	   (select tc.cityName from tf_city tc where tc.id=T.toCityId) [name],
	   count(*) ViewCount from 
(select ed.*,
	   (select tc.countryId from tf_city tc where tc.id=ed.toCityId) CountryId,
       (select tc.provinceId from tf_city tc where tc.id=ed.toCityId) ProvinceId
 from tf_eventdetail ed) T where T.ProvinceId=@provinceId group by T.toCityId

GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].TF_SelectEventHByCityId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].TF_SelectEventHByCityId
GO

CREATE PROCEDURE [dbo].TF_SelectEventHByCityId (
	@cityId int,
	@PageSize int = 5,
	@PageIndex int = 1
)
as 

SET NOCOUNT ON

select * from (
	select h.*,row_number() over(order by h.id) rowid from tf_eventh h where h.id in 
		(select eventHId from tf_eventdetail where toCityId=@cityId)
)T
where T.rowid between ((@PageIndex)*(@PageSize)-(@PageSize)+1) and (@PageIndex)*(@PageSize) 

GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].TF_SelectEventHByProvinceId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].TF_SelectEventHByProvinceId
GO

CREATE PROCEDURE [dbo].TF_SelectEventHByProvinceId (
	@provinceId int,
	@PageSize int = 5,
	@PageIndex int = 1
)
as 

SET NOCOUNT ON

select * from (
	select h.*, row_number() over(order by h.id) rowid from tf_eventh h where h.id in (select ed.eventhid from 
		tf_eventdetail ed inner join 
		tf_city city on ed.toCityId=city.id
	where city.provinceId=@provinceId)
) T
where T.rowid between ((@PageIndex)*(@PageSize)-(@PageSize)+1) and (@PageIndex)*(@PageSize) 

GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].TF_SelectEventHByCountryId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].TF_SelectEventHByCountryId
GO

CREATE PROCEDURE [dbo].TF_SelectEventHByCountryId (
	@countryId int,
	@PageSize int = 5,
	@PageIndex int = 1
)
as 

SET NOCOUNT ON

select * from (
	select h.*, row_number() over(order by h.id) rowid from tf_eventh h where h.id in 
		(select ed.eventhId from 
		tf_eventdetail ed inner join 
		tf_city city on ed.toCityId=city.id
		where city.countryId=@countryId)
) T 
where T.rowid between ((@PageIndex)*(@PageSize)-(@PageSize)+1) and (@PageIndex)*(@PageSize) 

GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].TF_SelectViewByKeyword') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].TF_SelectViewByKeyword
GO

CREATE PROCEDURE [dbo].TF_SelectViewByKeyword (
	@keywords nvarchar(1000),
	@PageSize int = 10,
	@PageIndex int = 1
)
as 
  declare @sql nvarchar(3000);
  declare @startIndex int;
  declare @endIndex int;
  set @startIndex = ((@PageIndex)*(@PageSize)-(@PageSize)+1);
  set @endIndex = (@PageIndex)*(@PageSize);

  set @sql = 'select T.* from (select id,url,[name],detail,row_number() over(order by id) rowid from tf_view where contains([name],''' + replace(@keywords,' ',' or ') + '''))T 
				where T.rowid between ' + cast(@startIndex as varchar(1000)) + ' and ' + cast(@endIndex as varchar(1000))
  exec sp_executesql @sql
GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].TF_SelectViewByKeywordCount') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].TF_SelectViewByKeywordCount
GO

CREATE PROCEDURE [dbo].TF_SelectViewByKeywordCount (
	@keywords nvarchar(1000)
)
as 
  declare @sql nvarchar(3000);

  set @sql = 'select count(*) from tf_view where contains([name],''' + replace(@keywords,' ',' or ') + ''')';
  exec sp_executesql @sql
GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].TF_SelectEventHByKeyword') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].TF_SelectEventHByKeyword
GO

CREATE PROCEDURE [dbo].TF_SelectEventHByKeyword (
	@keywords nvarchar(1000),
	@PageSize int = 10,
	@PageIndex int = 1
)
as 
  declare @sql nvarchar(3000);
  declare @startIndex int;
  declare @endIndex int;
  set @startIndex = ((@PageIndex)*(@PageSize)-(@PageSize)+1);
  set @endIndex = (@PageIndex)*(@PageSize);

  set @sql = 'select T.* from (select id,EventTitle,PicUrl,StartDate,AttendCount,row_number() over(order by id) rowid from tf_eventh where contains(EventTitle,''' + replace(@keywords,' ',' or ') + '''))T 
				where T.rowid between ' + cast(@startIndex as varchar(1000)) + ' and ' + cast(@endIndex as varchar(1000))
  exec sp_executesql @sql
GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].TF_SelectEventHByKeywordCount') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].TF_SelectEventHByKeywordCount
GO

CREATE PROCEDURE [dbo].TF_SelectEventHByKeywordCount (
	@keywords nvarchar(1000)
)
as 
  declare @sql nvarchar(3000);

  set @sql = 'select count(*) from tf_eventh where contains(EventTitle,''' + replace(@keywords,' ',' or ') + ''')';
  exec sp_executesql @sql
GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].TF_SelectCityNavigator') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].TF_SelectCityNavigator
GO

CREATE PROCEDURE [dbo].TF_SelectCityNavigator (
	@cityId int
)
as 
select c.countryID,(select n.countryName from tf_country n where n.id=c.countryID) countryName,
       c.id,c.CityName,
       c.provinceID,(select p.provice_Name from tf_province p where p.id=c.provinceID) provinceName
from tf_city c where c.id=@cityId
GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TF_UserProfile_Login]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[TF_UserProfile_Login]

GO

CREATE PROCEDURE [dbo].[TF_UserProfile_Login] (
	@Email varchar(50),
	@pwd    varchar(16)
)
AS
select * from tf_userprofile where email=@Email and pwd=@pwd
GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].TF_UserProfile_LoginByEmail') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].TF_UserProfile_LoginByEmail

GO

CREATE PROCEDURE [dbo].TF_UserProfile_LoginByEmail (
	@Email varchar(50)
)
AS
select * from tf_userprofile where email=@Email
GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].TF_SelectTunyEventH') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].TF_SelectTunyEventH
GO

CREATE PROCEDURE [dbo].TF_SelectTunyEventH 
as 
  select T.* from (
		select v.*,row_number() over(order by id) rowid  from VW_Tuny_EventH v) T
  where T.rowid<=4
GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].TF_DeleteEdAndEvs') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].TF_DeleteEdAndEvs
GO

CREATE PROCEDURE [dbo].TF_DeleteEdAndEvs (
	@edId int,
	@OwnerId int
)
as 
	delete from tf_eventview where cityId in 
		(select tocityId from tf_eventDetail 
			where id=@edId and eventHid in 
				(select id from tf_eventH where ownerId=@OwnerId)
		);
	delete from tf_eventDetail where id=@edId and eventHid in 
				(select id from tf_eventH where ownerId=@OwnerId);
GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].TF_DeleteEventDetailByDayNum') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].TF_DeleteEventDetailByDayNum
GO

CREATE PROCEDURE [dbo].TF_DeleteEventDetailByDayNum (
	@dayNum int,
	@OwnerId int
)
as 
	delete from tf_eventview where cityId in 
		(select tocityId from tf_eventDetail 
			where dayNumber=@dayNum and eventHid in 
				(select id from tf_eventH where ownerId=@OwnerId)
		);
	delete from tf_eventDetail where dayNumber=@dayNum and eventHid in 
				(select id from tf_eventH where ownerId=@OwnerId);
GO
------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TF_UserProfile_Reg_Check]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[TF_UserProfile_Reg_Check]

GO

CREATE PROCEDURE [dbo].[TF_UserProfile_Reg_Check] (
	@p_Email varchar(50),
	@p_Nick_Name    varchar(50)
)
AS
	select count(*),0 from tf_userprofile where email=@p_Email;
	select count(*),1 from tf_userprofile where Nick_Name=@p_Nick_Name;
GO
------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tf_event_logSelectAllOrderByDate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[tf_event_logSelectAllOrderByDate]
GO

CREATE PROCEDURE [dbo].[tf_event_logSelectAllOrderByDate]

AS

SET NOCOUNT ON

SELECT
	[id],
	[uid],
	[user_nick_name],
	[user_pic],
	[ele_id],
	[ele_name],
	[log_type],
	[keyin_date]
FROM
	[tf_event_log]
order by keyin_date desc
GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tf_attendSelectByEhId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[tf_attendSelectByEhId]
GO

CREATE PROCEDURE [dbo].[tf_attendSelectByEhId] (
	@ehId int
)

AS

SET NOCOUNT ON

select *
from Vw_attend_user
WHERE
	[ehId] = @ehId
GO
------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tf_attentionSelectByEhId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[tf_attentionSelectByEhId]
GO

CREATE PROCEDURE [dbo].[tf_attentionSelectByEhId] (
	@ehId int
)

AS

SET NOCOUNT ON

select *
from Vw_attention_user
WHERE
	[ehId] = @ehId
GO
------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tf_albumSelectByUserId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[tf_albumSelectByUserId]
GO

CREATE PROCEDURE [dbo].[tf_albumSelectByUserId] (
	@uid int
)

AS

SET NOCOUNT ON

SELECT
	[id],
	[album_name],
	[owner_id],
	[keyin_date],
	[memo]
FROM
	[tf_album]
WHERE
	[owner_id] = @uid
GO
------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].SelectAlbumListByOwner') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].SelectAlbumListByOwner
GO

CREATE PROCEDURE [dbo].SelectAlbumListByOwner (
	@uid int
)

AS

SET NOCOUNT ON

SELECT
	*
FROM
	vw_album_list
WHERE
	[owner_id] = @uid
GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TF_picsSelectByAlbumId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[TF_picsSelectByAlbumId]
GO

CREATE PROCEDURE [dbo].[TF_picsSelectByAlbumId] (
	@uid int,
	@AlbumId int,
	@PageSize int = 1,
	@PageIndex int = 1
)

AS

SET NOCOUNT ON

if @AlbumId<>0
begin
select 
* 
from 
(select tp.*,row_number() over(order by id) rowid  from tf_pics tp where tp.owner_id = @uid and tp.album_id = @AlbumId) T 
	where T.rowid between ((@PageIndex)*(@PageSize)-(@PageSize)+1) and (@PageIndex)*(@PageSize)  
select count(*) from tf_pics tp where tp.owner_id = @uid and tp.album_id = @AlbumId
end
else
begin
select 
* 
from 
(select tp.*,row_number() over(order by id) rowid  from tf_pics tp where tp.owner_id = @uid) T 
	where T.rowid between ((@PageIndex)*(@PageSize)-(@PageSize)+1) and (@PageIndex)*(@PageSize)  
select count(*) from tf_pics tp where tp.owner_id = @uid
end

GO
------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TF_BlogSelectByOwnerId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[TF_BlogSelectByOwnerId]
GO

CREATE PROCEDURE [dbo].[TF_BlogSelectByOwnerId]
(
	@uid int,
	@PageSize int = 10,
	@PageIndex int = 1
)

AS

SET NOCOUNT ON
select T.* from (
	 select b.*,row_number() over(order by id) rowid FROM [TF_Blog] b WHERE b.[owner_id] = @uid
) T where T.rowid between ((@PageIndex)*(@PageSize)-(@PageSize)+1) and (@PageIndex)*(@PageSize)  

select count(*) from tf_blog b WHERE b.[owner_id] = @uid

GO
------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TF_BlogTmpSelectByOwnerId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[TF_BlogTmpSelectByOwnerId]
GO

CREATE PROCEDURE [dbo].[TF_BlogTmpSelectByOwnerId]
(
	@uid int,
	@PageSize int = 10,
	@PageIndex int = 1
)

AS

SET NOCOUNT ON
select T.* from (
	 select b.*,row_number() over(order by id) rowid FROM tf_blog_tmp b WHERE b.[owner_id] = @uid
) T where T.rowid between ((@PageIndex)*(@PageSize)-(@PageSize)+1) and (@PageIndex)*(@PageSize)  

select count(*) from tf_blog_tmp b WHERE b.[owner_id] = @uid

GO
------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TF_BlogSelectByOwnerIdAndBlogId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[TF_BlogSelectByOwnerIdAndBlogId]
GO

CREATE PROCEDURE [dbo].[TF_BlogSelectByOwnerIdAndBlogId]
(
	@uid int,
	@blogId int
)

AS

SET NOCOUNT ON

	select b.*,row_number() over(order by id) rowid FROM [TF_Blog] b WHERE b.[owner_id] = @uid and b.id = @blogId 
	
	declare @curRowid int
	select @curRowid=a.rowid from (select b.*,row_number() over(order by id) rowid FROM [TF_Blog] b WHERE b.[owner_id] = @uid) a WHERE a.id = @blogId

	select T.id from (select b.*,row_number() over(order by id) rowid FROM [TF_Blog] b WHERE b.[owner_id] = @uid) T where T.rowid=(@curRowid-1)
	select T.id, T.blog_title from (select b.*,row_number() over(order by id) rowid FROM [TF_Blog] b WHERE b.[owner_id] = @uid) T where T.rowid=(@curRowid+1)
		
	
GO
------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TF_BlogDeleteCasecade]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[TF_BlogDeleteCasecade]
GO

CREATE PROCEDURE [dbo].[TF_BlogDeleteCasecade] (
	@ID int
)

AS

SET NOCOUNT ON

DELETE FROM
	[TF_Blog]
WHERE
	[ID] = @ID
delete from tf_leavewords where elementid=@ID and elementtype='blg'
GO

------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TF_BlogTmpSelectCountByOwnerId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[TF_BlogTmpSelectCountByOwnerId]
GO

CREATE PROCEDURE [dbo].[TF_BlogTmpSelectCountByOwnerId]
(
	@uid int
)

AS

select count(*) from tf_blog_tmp b WHERE b.[owner_id] = @uid

GO